﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using LibUtilities.dal;

namespace CLibArtreve.dal
{
    public class UserArtDAL : SqlDatabase
    {
        #region CONSTRUCTORS
        public UserArtDAL(string connectionString) : base(connectionString) { }
        #endregion

        #region OVERRIDE METHODS
        public override string ToString()
        {
            return "DATA ACCES LAYER: " + this.GetType().ToString();
        }
        #endregion

        #region GET METHODS
        public DataSet GetAllUserArts()
        {
            try
            {
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLUSERART, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetArtByUserId()
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = 1;
                parameters.Add(parameter);

                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLARTSBYUSERID, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetUserByArtId(Int64? id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_id";
                parameter.Value = id;
                parameters.Add(parameter);

                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLUSERSBYARTID, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool IsLikedByUser(Int64? art_id, Int64? user_id)
        {
            string sQuery = @"
                    SELECT COUNT(user_id)
                    FROM UserArts
                    WHERE user_id = @user_id AND art_id = @art_id";

            //MAKE SOME PARAMETERS
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = new SqlParameter();
            parameter.DbType = DbType.Int64;
            parameter.Direction = ParameterDirection.Input;
            parameter.ParameterName = "art_id";
            parameter.Value = art_id;
            parameters.Add(parameter);

            parameter = new SqlParameter();
            parameter.DbType = DbType.Int64;
            parameter.Direction = ParameterDirection.Input;
            parameter.ParameterName = "user_id";
            parameter.Value = user_id;
            parameters.Add(parameter);

            int count = (int)ExecuteScalarWithCommonQuery<Int32>(sQuery, parameters);
            if (count == 0)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region INSERT METHODS
        public int InsertLike(Int64? userArtUserId, Int64 userArtArtId)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                //MAKE PARAMETERS
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = userArtUserId;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_id";
                parameter.Value = userArtArtId;
                parameters.Add(parameter);

                //EXECUTE
                int p_userArt = ExecuteNonQueryWithCommonQuery(Queries.INSERTUSERART, parameters, true);
                if (p_userArt > 0)
                {
                    p_userArt = Convert.ToInt32(ExecuteScalarWithCommonQuery<decimal>(Queries.SELECTIDENTITYAFTERINSERTSQLSERVER));
                }
                return p_userArt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion

        #region DELETE METHODS
        public int DeleteUserArt(Int64? userArtUserId, Int64 userArtArtId) 
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                //MAKE PARAMETERS
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = userArtUserId;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_id";
                parameter.Value = userArtArtId;
                parameters.Add(parameter);

                //EXECUTE
                int p_userArt = ExecuteNonQueryWithCommonQuery(Queries.DELETEUSERART, parameters, true);
                
                return p_userArt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        #endregion
    }
}
